'@ on 20071123:Added for Sale Return.
Imports System.Data
Imports System.Data.SqlClient
Imports MyClasses
Imports System.Text

Public Class DSaleReturn
    Dim objLocSale As CSale
    Dim intSaleRtrnID As Integer = 0
    Dim SeqSaleReturnID As String = 0
    Dim intSaleID As Integer = 0
    Public Function SaveSaleReturn(ByRef ObjSale As CSale, ByRef objsaleItem() As CNGItem, ByRef dtbOtherCharges As DataTable, ByVal dtbTaxes As DataTable, ByVal SeqLocSaleReturnID As Integer) As Integer
        Dim dbConnection As New dbAccess
        Dim intResult As Integer = 0
        intSaleID = ObjSale.SaleBillID
        Try
            objLocSale = ObjSale
            dbConnection.BeginTransaction()
            If SeqLocSaleReturnID > 0 Then
                SeqSaleReturnID = SeqLocSaleReturnID
                UpdateSaleReturn(ObjSale, dbConnection, SeqSaleReturnID)
            Else
                intSaleRtrnID = SaveSaleReturn(ObjSale, dbConnection)
            End If
            If intSaleRtrnID > 0 Then
                If SeqLocSaleReturnID > 0 Then
                                     '
                    intResult = dbConnection.ExecuteNonQuery("DELETE FROM tSaleReturnDet WHERE SaleReturnID=" & intSaleRtrnID.ToString())
                End If
                If UpdateSaleReturnItemStocks(intSaleRtrnID, objsaleItem, dbConnection) = 0 Then
                    intResult = 0
                    Exit Function
                End If
                objLocSale.intSRV_Number = intSaleRtrnID
                intResult = SaveSaleItemDetail(intSaleRtrnID, objsaleItem, dbConnection)
            Else
                Exit Function
            End If
            If dtbTaxes.Rows.Count > 0 AndAlso funPriSaveTaxes(dtbTaxes, dbConnection) = 0 Then
                intResult = 0
                Exit Function
            End If
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "Save")
            intResult = 0
        Finally
            If intResult = 0 Then
                intSaleRtrnID = 0
                dbConnection.RollBackTransaction()
            Else
                dbConnection.CommitTransaction()
            End If
            dbConnection.closeSqlcon()
            If intSaleRtrnID = 0 Then
                SaveSaleReturn = 0
            Else
                SaveSaleReturn = SeqSaleReturnID
            End If
            'Save = intSaleID
        End Try
    End Function

    Private Function funPriSaveTaxes(dtbTax As DataTable, ByRef dbConnection As dbAccess) As Int16
        dbConnection.ExecuteNonQuery("DELETE FROM tSaleReturnTax WHERE SaleID=" & intSaleRtrnID.ToString())
        Dim param(4) As SqlParameter
        Dim strSql As String = "INSERT INTO tSaleReturnTax(SaleID,TaxID,TAX,TaxableAmt,TaxAmt)" & _
                                              "VALUES(@SaleID,@TaxID,@TAX,@TaxableAmt,@TaxAmt)"
        'Dim intResult As Integer
        For Each taxRow As DataRow In dtbTax.Rows
            If taxRow.Item(2) > 0 Then

                param(0) = New SqlParameter("@SaleID", intSaleRtrnID)
                param(1) = New SqlParameter("@TaxID", taxRow.Item(0))
                param(2) = New SqlParameter("@TAX", taxRow.Item(1))
                param(3) = New SqlParameter("@TaxableAmt", taxRow.Item(2))
                param(4) = New SqlParameter("@TaxAmt", taxRow.Item(3))
                If dbConnection.ExecuteNonQuery(strSql, param) = 0 Then
                    Return 0
                End If
            End If

        Next
        Return 1
    End Function
    Public Function SaveSaleReturn(ByRef ObjSale As CSale, ByRef dbConnection As dbAccess) As Integer

        Dim strSql As String = ""
        Dim param(19) As SqlParameter
        Try
            strSql = "INSERT INTO tSaleReturn(SaleID,SeqSRNID,PartyCode,SaleDate,PromoterID,SiteID,Amount,OtherCharges,TotalTaxes,Discount,GrossAmount,ShipingAddress,Status,CU,CD,LU,LD,FinYrID,SaleType,PriceListID)" & _
                     "VALUES(@SaleID,@SeqSRNID,@PartyCode,@SaleDate,@PromoterID,@SiteID,@Amount,@OtherCharges,@TotalTaxes,@Discount,@GrossAmount,@ShipingAddress,@Status,@CU,@CD,@LU,@LD,@FinYrID,@SaleType,@PriceListID)"
            ''''''''''''''''ShipingAddress,Status,CU,CD,LU,LD,FinYrID,SaleType

            SeqSaleReturnID = DGeneral.GetSeqNum(IIf(ObjSale.SaleType = DGeneral.DocTypes.INVOICE_RETURN, DGeneral.DocTypes.CASH_MEMO_RETRUN, ObjSale.SaleType), dbConnection, True)
            param(0) = New SqlParameter("@SaleID", ObjSale.SaleBillID)
            param(1) = New SqlParameter("@SeqSRNID", SeqSaleReturnID)
            param(2) = New SqlParameter("@PartyCode", ObjSale.PartyID)
            param(3) = New SqlParameter("@SaleDate", ObjSale.SaleDate)
            param(4) = New SqlParameter("@PromoterID", ObjSale.PromoterID)
            param(5) = New SqlParameter("@SiteID", ObjSale.Site)
            param(6) = New SqlParameter("@Amount", ObjSale.Amount)
            param(7) = New SqlParameter("@OtherCharges", ObjSale.dblOtherCharges)
            param(8) = New SqlParameter("@TotalTaxes", ObjSale.TotalTax)
            param(9) = New SqlParameter("@Discount", ObjSale.Discount)
            param(10) = New SqlParameter("@GrossAmount", ObjSale.GrossAmt)
            param(11) = New SqlParameter("@ShipingAddress", ObjSale.ShippingAdress)
            param(12) = New SqlParameter("@Status", "1") 'ACTIVE
            param(13) = New SqlParameter("@CU", CCommon.GetUserID)
            param(14) = New SqlParameter("@CD", Date.Now.Date())
            param(15) = New SqlParameter("@LU", CCommon.GetUserID)
            param(16) = New SqlParameter("@LD", Date.Now.Date())
            param(17) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)
            param(18) = New SqlParameter("@SaleType", ObjSale.SaleType)
            param(19) = New SqlParameter("@PriceListID", ObjSale.priceListID)
            If dbConnection.ExecuteNonQuery(strSql, param) > 0 Then

                Return dbConnection.ExecuteScalar("SELECT TOP 1 SaleReturnID FROM tSaleReturn ORDER BY SaleReturnID DESC")
            Else
                Return 0
            End If


        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSale")
            Return 0
        End Try

    End Function
    Public Shared Function UpdateSaleReturn(ByRef ObjSale As CSale, ByRef dbConnection As dbAccess, ByVal intSaleReturnID As Integer) As Integer

        Dim strSql As String = ""
        Dim param(12) As SqlParameter
        Try
            strSql = "UPDATE tSaleReturn SET PartyCode=@PartyCode,PromoterID=@PromoterID,SiteID=@SiteID,Amount=@Amount,OtherCharges=@OtherCharges,TotalTaxes=@TotalTaxes,Discount=@Discount,GrossAmount=@GrossAmount,ShipingAddress=@ShipingAddress,LU=@LU,LD=@LD,PriceListID=@PriceListID WHERE SaleReturnID=@SaleID"

            param(0) = New SqlParameter("@PartyCode", ObjSale.PartyID)
            param(1) = New SqlParameter("@PromoterID", ObjSale.PromoterID)
            param(2) = New SqlParameter("@SiteID", ObjSale.Site)
            param(3) = New SqlParameter("@Amount", ObjSale.Amount)
            param(4) = New SqlParameter("@OtherCharges", ObjSale.dblOtherCharges)
            param(5) = New SqlParameter("@TotalTaxes", ObjSale.TotalTax)
            param(6) = New SqlParameter("@Discount", ObjSale.Discount)
            param(7) = New SqlParameter("@GrossAmount", ObjSale.GrossAmt)
            param(8) = New SqlParameter("@ShipingAddress", ObjSale.ShippingAdress)

            param(9) = New SqlParameter("@LU", CCommon.GetUserID)
            param(10) = New SqlParameter("@LD", Date.Now.Date())
            param(11) = New SqlParameter("@PriceListID", ObjSale.priceListID)
            param(12) = New SqlParameter("@SaleID", intSaleReturnID)

            Return dbConnection.ExecuteNonQuery(strSql, param)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSale")
            Return 0
        End Try

    End Function

    Public Function UpdateSaleReturnItemStocks(ByVal SaleID As Integer, ByRef objsaleItem() As CNGItem, ByRef dbConnection As dbAccess) As Integer

        'Dim dtDItem As DataTable = GetSaleReturnBillItems(SaleID)
        'Dim objNgItem As CNGItem = New CNGItem
        Dim i As Integer
        Try
            For Each objNgItem As CNGItem In objsaleItem

                If objLocSale.SaleType <> DGeneral.DocTypes.QUOTATION Then
                    'objNgItem.isTinTin = SaleItem.Item("IsTinTin")
                    'objNgItem.Quantity = SaleItem.Item("Qty")
                    'objNgItem.intPackUpdateID = SaleItem.Item("PackUpdateIDPK")
                    If DGeneral.AddNGItemStock(objNgItem, dbConnection) = 0 Then
                        Return 0
                    End If
                    'Tasedet SaleID, ItemID, CLRcode, PackUpdateID, SalePrice, Qty, RetQty, TaxID, TaxAmt, DISC, DISC_AMT, Amount, SchemeDiscount, FinYrID
                    '"UPDATE tSaleReturnDet SET                     SaleReturnID, ItemID, CLRcode, PackUpdateID, SalePrice, Qty, SoldQty, TaxID, TaxAmt, DISC, DISC_AMT, Amount, SchemeDiscount, FinYrID
                    Dim stQuery As String = "UPDATE tSaleDet SET RetQty = RetQty + " & objNgItem.Quantity.ToString & " WHERE SaleID = " & intSaleID.ToString() & " AND ItemID =" & objNgItem.ItemID.ToString() & " AND   CLRcode  =" & objNgItem.ColorCode.ToString() & " AND     PackUpdateID =" & objNgItem.intPackUpdateDtl_PK.ToString() & " AND  SalePrice =" & objNgItem.dblRate.ToString()
                    If dbConnection.ExecuteNonQuery(stQuery) = 0 Then
                        Return 0
                    End If
                    'If DGeneral.SubstractNGItemStock(objNgItem, dbConnection) = 0 Then
                    '    Return 0
                    'End If
                End If
            Next
            Return 1
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSaleItemDetail", "")
            Return 0
        End Try
    End Function
    Public Function SaveSaleItemDetail(ByVal SaleID As Integer, ByRef objsaleItem() As CNGItem, ByRef dbConnection As dbAccess) As Integer


        Dim strSql As String = String.Empty
        Dim param(13) As SqlParameter

        Dim intRecordsUpdate As Integer
        Dim i As Integer
        Try
            For Each Item As CNGItem In objsaleItem
                strSql = " INSERT INTO tSaleReturnDet(SaleReturnID,ItemID,CLRcode,PackUpdateID,SalePrice,SoldQty,Qty,Amount,DISC,DISC_AMT,TAXID,TAXAMT,SchemeDiscount,FinYrId)" & _
                         " VALUES(@SaleID,@ItemID,@CLRcode,@PackUpdateID,@SalePrice,@SoldQty,@Qty,@Amount,@DISC,@DISC_AMT,@TAXID,@TAXAMT,@SchemeDiscount,@FinYrId)"

                param(0) = New SqlParameter("@SaleID", SaleID)
                param(1) = New SqlParameter("@ItemID", Item.ItemID)
                param(2) = New SqlParameter("@CLRcode", Item.ColorCode)
                param(3) = New SqlParameter("@PackUpdateID", Item.intPackUpdateDtl_PK)
                param(4) = New SqlParameter("@SalePrice", Item.dblRate)
                param(5) = New SqlParameter("@Qty", Item.intReceivedQuantity)
                param(6) = New SqlParameter("@Amount", Item.Amount)
                param(7) = New SqlParameter("@DISC", Item.strDisc)
                param(8) = New SqlParameter("@DISC_AMT", Item.dblDISCAmt)
                param(9) = New SqlParameter("@TAXID", Item.TaxID)
                param(10) = New SqlParameter("@TAXAMT", Item.TaxAmt)
                param(11) = New SqlParameter("@SchemeDiscount", Item.SchemeDiscount)
                param(12) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)
                param(13) = New SqlParameter("@SoldQty", Item.intSoldQuantity)
                i = dbConnection.ExecuteNonQuery(strSql, param)
                If i = 0 Then
                    Return 0
                End If

                If objLocSale.SaleType <> DGeneral.DocTypes.QUOTATION Then
                    If DGeneral.AddNGItemStock(Item, dbConnection) = 0 Then
                        Return 0
                    End If
                End If
                intRecordsUpdate += i

                'If ConfigurationManager.AppSettings("TAX") = 1 Then 'CHECK FOR TAX (T0)
                '    SaveSaleTaxDetails(SaleID, Item.ItemID, Item.CLRcode, Item.Amount, dbConnection)
                'End If
                ' ClearCounterItem(Item.CounterSeqID, dbConnection)
                'DPurchaseInvoice.SaveStockRegister(Item.ItemID, Item.Quantity, Now.Date, "S", dbConnection)
            Next
            Return i
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSaleItemDetail", strSql)
            Return 0
        End Try
    End Function

    'Private Function subpriAddSaleStock(intPackUpdateID As Integer, intQty As Integer, ByRef dbConnection As dbAccess) As Integer
    '    Dim strSql As String = "UPDATE PackingUpdateDetail SET Stock=Stock +" & intQty.ToString() & " WHERE ID=" + intPackUpdateID.ToString()

    '    Return dbConnection.ExecuteNonQuery(strSql)

    'End Function
    'Private Function subpriSubstractSaleStock(intPackUpdateID As Integer, intQty As Integer, ByRef dbConnection As dbAccess) As Integer
    '    Dim strSql As String = "UPDATE PackingUpdateDetail SET Stock=Stock - " & intQty.ToString() & " WHERE ID=" + intPackUpdateID.ToString()

    '    Return dbConnection.ExecuteNonQuery(strSql)

    'End Function



    Public Shared Function SaveSaleTaxDetails(ByVal BillID As Long, ByVal ItemID As Long, ByVal CLRcode As String, ByVal Amount As Double, ByRef dbConnection As dbAccess) As Integer
        Dim strSql As String = ""
        Dim param(8) As SqlParameter
        Dim intResult As Integer
        Try
            Dim dtDItemTaxes As New DataTable ' = dtDItemTaxes(
            dtDItemTaxes = DTax.GetItemTaxes(ItemID, CLRcode, Amount)
            If IsNothing(dtDItemTaxes) Then Return 1
            For Each Tax As DataRow In dtDItemTaxes.Rows

                strSql = " INSERT INTO tBillItemTax(BillItemSeqid,ItemId,BillID,BillType,CLRcode, " & _
                                     " FormulaId,TaxID,TaxPercent,TaxAmount) " & _
                                     " VALUES (@BillItemSeq,@ItemID,@BillID,@BillType,@CLRcode,@FormulaId,@TaxID,@TaxPerc,@TaxAmount)"

                param(0) = New SqlParameter("@BillItemSeq", DGeneral.GetSeqNum("BillItemTax", dbConnection, True))
                param(1) = New SqlParameter("@ItemID", ItemID)
                param(2) = New SqlParameter("@BillID", BillID)
                param(3) = New SqlParameter("@BillType", "S")
                param(4) = New SqlParameter("@CLRcode", CLRcode)
                param(5) = New SqlParameter("@FormulaID", "")
                param(6) = New SqlParameter("@TaxID", Tax("ID"))
                param(7) = New SqlParameter("@TaxPerc", Tax("Val"))
                param(8) = New SqlParameter("@TaxAmount", Tax("Amount"))
                intResult = intResult + dbConnection.ExecuteNonQuery(strSql, param)
            Next

            Return intResult

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSaleTaxDetails", strSql)
            Return 0
        End Try
    End Function


    'Public Shared Function SaveSaleReturn(ByVal SeqReturnID As String, ByVal ReturnDate As Date, ByVal InvoiceID As Integer, ByVal Amount As Double, ByRef Items() As CReturnItem, ByVal Remark As String) As Integer
    '    Dim strSql As String = ""
    '    Dim param(8) As SqlParameter
    '    Dim dbConnection As New dbAccess
    '    Dim intReturnID As Integer
    '    Dim intResult As Integer
    '    Try
    '        dbConnection.BeginTransaction()

    '        strSql = "INSERT INTO tSaleReturn(SaleReturnID,SeqSaleReturnID,SaleID,SaleReturnDate,Amount,Remark,LU,LD,FinYrId) " & _
    '         " VALUES(@SaleReturnID,@SeqSaleReturnID,@SaleID,@SaleReturnDate,@Amount,@Remark,@LU,@LD,@FinYrId)"

    '        intReturnID = DGeneral.GetSeqNum("SR", dbConnection, True)
    '        param(0) = New SqlParameter("@SaleReturnID", intReturnID)
    '        param(1) = New SqlParameter("@SeqSaleReturnID", SeqReturnID)
    '        param(2) = New SqlParameter("@SaleID", InvoiceID)
    '        param(3) = New SqlParameter("@SaleReturnDate", ReturnDate)
    '        param(4) = New SqlParameter("@Amount", Amount)
    '        'param(5) = New SqlParameter("@Remark", Remark)
    '        'Modified By:-(2008/01/16)
    '        param(5) = New SqlParameter("@Remark", IIf(IsNothing(Remark), "", Remark))
    '        param(6) = New SqlParameter("@LU", CCommon.GetUserID)
    '        param(7) = New SqlParameter("@LD", Date.Now())
    '        param(8) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)
    '        intResult = dbConnection.ExecuteNonQuery(strSql, param)

    '        If intResult = 0 Then Exit Function
    '        intResult = SaveSaleReturnItems(intReturnID, Items, dbConnection)

    '        If intResult = 0 Then Exit Function
    '        intResult = DPurchaseInvoice.UpdtesaleReturnStock(Items, ReturnDate, dbConnection)

    '    Catch ex As Exception
    '        dbConnection.RollBackTransaction()
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSaleReturn", strSql)
    '        intResult = 0
    '    Finally

    '        If intResult > 0 Then
    '            dbConnection.CommitTransaction()
    '        Else
    '            intResult = 0
    '            dbConnection.RollBackTransaction()
    '        End If
    '        dbConnection.closeSqlcon()
    '        SaveSaleReturn = intReturnID
    '    End Try

    'End Function

    Public Shared Function SaveSaleReturnItems(ByVal ReturnID As Integer, ByRef Items() As CReturnItem, ByRef dbConnection As dbAccess) As Integer

        Dim strSql As String
        Dim param(7) As SqlParameter
        Dim i As Integer

        Try

            For Each Item As CReturnItem In Items
                If Not IsNothing(Item) Then
                    strSql = " INSERT INTO tSaleReturnDet(SaleReturnID,ItemID,CLRcode,Qty,ReturnPrice,Amount,Reason,FinYrId)" & _
                                              " VALUES(@SaleReturnID,@ItemID,@CLRcode,@Qty,@ReturnPrice,@Amount,@Reason,@FinYrId)"

                    param(0) = New SqlParameter("@SaleReturnID", ReturnID)
                    param(1) = New SqlParameter("@ItemID", Item.ItemID)
                    param(2) = New SqlParameter("@CLRcode", Item.CLRcode)
                    param(3) = New SqlParameter("@Qty", Item.Quantity)
                    param(4) = New SqlParameter("@ReturnPrice", Item.Rate)
                    param(5) = New SqlParameter("@Amount", Item.Amount)
                    'param(6) = New SqlParameter("@Reason", Item.Reason)
                    'Modified By:-(2008/01/16)
                    param(6) = New SqlParameter("@Reason", IIf(IsNothing(Item.Reason), "", Item.Reason))
                    param(7) = New SqlParameter("@FinYrId", CCommon.GetFinYrID)

                    i = i + dbConnection.ExecuteNonQuery(strSql, param)
                End If
            Next
            Return i

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "SaveSaleReturnItems")
            Return 0
        End Try
    End Function

#Region "GETDETAILS"
 
    Public Shared Function GetSaleBill(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.SeqSRNID,SaleID,S.PartyCode AS PartyID ,ISNULL(P.PartyCode,'') AS PartyCode,ISNULL(P.PartyName,'') AS PartyName, S.SaleDate,S.PromoterID,ISNULL(R.PromoterName,'') AS PromoterName, ISNULL(R.PromoterCode,'') AS PromoterCode ,S.SiteID,S.PriceListID,S.Amount,S.OtherCharges,S.TotalTaxes,S.Discount,S.GrossAmount,S.ShipingAddress,S.SaleType,ISNULL(T.SiteName,'') AS SiteName,ISNULL(T.SiteCode,'') AS SiteCode  FROM tSaleReturn S LEFT OUTER JOIN Party P ON(S.PartyCode = P.ID) LEFT OUTER JOIN Promoter R ON (S.PromoterID= R.ID) LEFT OUTER JOIN [Site] T ON(S.SiteID=T.ID) WHERE S.SaleReturnID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetSaleBill", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetSaleReturnBillItems(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.ItemID,P.ProductName,P.ProductCode,S.CLRcode AS CLRID,C.ColorCode,C.ColorDescription, S.PackUpdateID AS PackUpdateIDPK ,D.GodownId,D.PackingID,S.SalePrice,S.Qty,S.SoldQty,S.Amount,S.DISC,S.DISC_AMT,S.TAXID,S.TAXAMT, S.SchemeDiscount,G.GodownName,PK.packSize + ' ' + PK.PackingDescription AS PCK FROM tSaleReturnDet S INNER JOIN Product p ON(S.ItemID=P.ID) INNER JOIN COLOR C ON (S.CLRCODE=C.ID)INNER JOIN PackingUpdateDetail D ON(D.ID=S.PAckUpdateID)INNER JOIN PackingSize PK ON (D.PackSizeID=PK.ID) INNER JOIN Godown G ON(D.GodownID=G.ID) WHERE S.SaleReturnID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetSaleReturnBillItems", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetSaleTaxes(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT S.TaxID,S.TAX,S.TaxableAmt,S.TaxAmt, TaxAmt+ TaxableAmt AS TOTAL FROM tSaleTax S WHERE S.SaleID=" & intSearchID.ToString() & " UNION SELECT Id AS TAXID,TaxPercentage AS TAX,'0' AS TAXABLEAMT,'0' AS TAXAMT,'0' AS TOTAL FROM TAX WHERE IsDeleted=0 AND ID NOT IN(SELECT DISTINCT S.TaxID FROM  tSaleReturnTax S WHERE S.SaleReturnID=" & intSearchID.ToString() & ")"

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetSaleTaxes", strSql)
            Return Nothing
        End Try
    End Function


    Public Shared Function GetSaleBillOtherCharges(ByVal intSearchID As Int64) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT O.ID,O.Chargename,ISNULL(S.Value,0)AS Value,ISNULL(S.Amount,0) AS Amount,O.IsTaxable FROM tSaleOtherCharges S RIGHT OUTER JOIN OtherCharge O ON (S.OtherChargeID=O.ID) WHERE O.IsDeleted =0 AND O.AddToInvoice =1 AND S.SaleID=" & intSearchID.ToString()

            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "ClearCounterItem", strSql)
            Return Nothing
        End Try
    End Function


    Public Shared Function GetItemUnit(ByVal ItemID As Integer, ByVal CLRcode As String) As String
        Try
            Return DbHelper.ExecuteScalar("SELECT SellUnit FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetItemUnit")
            Return ""
        End Try
    End Function

    Public Shared Function GetItemPackUnit(ByVal ItemID As Integer, ByVal CLRcode As String) As DataTable
        Try
            Return DbHelper.ExecuteDataTable("SELECT SellPack,SellUnit,ExpDate,ConversionFactor FROM tStock WHERE ItemID=" & ItemID & " AND CLRcode='" & CLRcode & "'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetItemPackUnit")
            Return Nothing
        End Try
    End Function

    'Public Shared Function GetCounterData(ByVal CounterID As Integer) As DataTable
    '    Dim strSql As String = ""
    '    Try
    '        strSql = "SELECT SeqID,ItemID,CLRcode,SaleUnit,SalePrice,Qty,Amount,SchemeID,schemeItemQty,SchemeDiscount,FreeReplacement,StockUpdate,Status FROM wSaleDet WHERE CounterID=" & CounterID & " Order by SeqID,Status"
    '        Return DbHelper.ExecuteDataTable(strSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCounterData", strSql)
    '        Return Nothing
    '    End Try
    'End Function

    Public Shared Function GetWItem(ByVal WorkingSeqID As Integer, ByRef dbConnection As dbAccess) As DataTable
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT  ItemID,CLRcode,Qty,Amount,schemeItemQty,StockID FROM wSaleDet WHERE SeqID=" & WorkingSeqID
            Return dbConnection.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetWItem", StrSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetWItem(ByVal WorkingSeqID As Integer) As DataTable
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT  ItemID,CLRcode,Qty,Amount,schemeItemQty,StockID FROM wSaleDet WHERE SeqID=" & WorkingSeqID
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetWItem", StrSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetWItemDetail(ByVal WorkingSeqID As Integer) As String
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT StockID FROM wSaleDet WHERE SeqID=" & WorkingSeqID
            Return DbHelper.ExecuteScalar(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetWItemDetail", StrSql)
            Return ""
        End Try
    End Function

    Public Shared Function GetWItemsDetails(ByVal WorkingSeqID As String) As DataTable
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT ItemID ,CLRcode,Qty ,SchemeID,schemeItemQty,VendorID,StockID FROM wSaleDet WHERE SeqID IN(" & WorkingSeqID & ")"
            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetWItemsDetails", StrSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetSearchItems(ByVal SellRecNo As Integer) As DataTable
        Dim StrSql As String
        Try
            StrSql = "SELECT sdet.SaleReturnID,sdet.ItemID,sdet.CLRcode,sdet.Qty,sdet.ReturnPrice,sdet.Amount,sdet.Reason," & _
                     " mp.seqitemid,mp.itemname, " & _
                     " tsr.SaleID,tsr.SaleReturnDate,tsr.Amount,tsr.Remark, " & _
                     " sdet.ItemID, sdet.CLRcode" & _
                     " FROM tSaleReturnDet sdet,tSaleReturn tsr,mpitem mp " & _
                     " WHERE mp.itemid=sdet.itemid AND tsr.SaleReturnID=sdet.SaleReturnID AND " & _
                     " sdet.SaleReturnID=" & SellRecNo

            Return DbHelper.ExecuteDataTable(StrSql)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetSearchItems")
            Return Nothing
        End Try
    End Function

    'Written By:- (20071029)    'Select Holiday Date    ' Query Modified By  
    'Public Shared Function IsHoliday() As Integer
    '    Dim StrSql As String
    '    Try
    '        'StrSql = "SELECT COUNT(*) FROM mholidays WHERE holidaydate='" & CurDate & "'"
    '        StrSql = "SELECT COUNT(*) FROM mHolidays WHERE DateDiff (D,getdate(),Holidaydate) =0"
    '        Return DbHelper.ExecuteScalar(StrSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "IsHoliday")
    '        Return Nothing
    '    End Try
    'End Function

    'Public Shared Function GetCounterAmount(ByVal CounterNo As Byte) As Double
    '    Try
    '        Dim strSql As String = "SELECT SUM (Amount) as Amount FROM wSaleDet WHERE CounterID=" & CounterNo
    '        Return DbHelper.ExecuteScalar(strSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCounterAmount")
    '        Return 0
    '    End Try
    'End Function
    'Public Shared Function CheckForSchedule(ByVal intCounters As Byte) As Integer
    '    Try
    '        Dim strSql As String = "SELECT I.ScheduleDrug,warnMessage " & _
    '                               "FROM wSaleDet W INNER JOIN mpItem I ON W.ItemID=I.ItemID " & _
    '                               "LEFT OUTER JOIN mWarning ON  mWarning.warnValue=I.ScheduleDrug " & _
    '                               "WHERE W.CounterID<=" & intCounters & _
    '                               " AND WarnModule='SALE' AND warnBase='SCHEDULE' "
    '        If DbHelper.ExecuteDataTable(strSql).Rows.Count > 0 Then
    '            Return 1
    '        Else : Return 0
    '        End If
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "CheckForSchedule")
    '        Return 0
    '    End Try
    'End Function

    Public Shared Function GetStockIDS(ByVal CounterSeqID As Integer, ByRef dbConnection As dbAccess) As String
        Dim strStocks As New StringBuilder
        Try
            'COMMA SEPAREATED  'StockID,Quantity,SchemeItemQty VALUES
            Dim strVAL As String
            strVAL = dbConnection.ExecuteScalar("SELECT StockID FROM wSaleDet WHERE SeqID=" & CounterSeqID)
            Dim strStockDtls() As String = strVAL.Split(",")

            For Each Value As String In strStockDtls
                If Value = "" Then Exit For
                strStocks.Append(Mid(Value, 1, InStr(Value, "-", ) - 1) & ",")
            Next
            If strStocks.Length > 0 Then strStocks.Replace(",", "", strStocks.Length - 1, 1)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetStockIDS")
            strStocks = Nothing
        Finally
            GetStockIDS = strStocks.ToString()
        End Try
    End Function
#End Region '"GETDETAILS"
    Public Shared Function GetParty() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT P.Id,P.PartyCode,P.PartyName FROM [NGSPaintsDB].[dbo].Party P WHERE P.IsDeleted=0")
    End Function
    Public Shared Function GetPromoter() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT P.Id,P.PromoterCode,P.PromoterName FROM [NGSPaintsDB].[dbo].promoter P WHERE P.IsDeleted=0")
    End Function
    Public Shared Function GetColors() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id],[ColorCode],[ColorDescription] FROM [NGSPaintsDB].[dbo].[Color] WHERE [IsDeleted]=0")
    End Function

    Public Shared Function GetColors(ByVal intColumnIndex As Integer, ByRef RecordCount As Integer, ByVal intProductID As Integer) As SqlDataReader
        'Returns The ItemList used for Sale Item List
        Dim StrSql As String = ""
        Try

            'StrSql = "SELECT  mpItem.ItemID,SeqItemID AS  Code,ItemName AS  Name,mfctrShortName AS  Company," & _
            '             " ISNULL((select SUM(Quantity ) FROM tStock WHERE DateDiff (D,getdate(),tStock.ExpDate)  >=0 AND tStock.ItemID = mpItem.ItemID  ),0) AS  Stock," & _
            '             " 0 LocationCode,mpItem.minreorderlevel AS  ReorderQty ,ScheduleDrug AS  Schedule,ELStock AS  EmergStock " & _
            '             " FROM mpItem INNER JOIN  mManufacturer ON mManufacturer.mfctrID = mpItem.mfctrID " & _
            '             " ORDER BY "

            'SELECT P.[Id],P.[ProductCode] AS Code,P.[ProductName] AS Name,S.SupplierName AS Company,R.PricePerPack AS Rate 
            StrSql = "SELECT C.Id,C.ColorCode,C.ColorDescription FROM [NGSPaintsDB].[dbo].[Color] C INNER JOIN [NGSPaintsDB].[dbo].[ProductColor] P ON (P.COLORID  =C.ID) WHERE C.IsDeleted=0  AND P.ProductID=" & intProductID.ToString() & " ORDER BY "
            ''StrSql = "SELECT C.Id,C.ColorCode,C.ColorDescription,S.Quantity FROM [NGSPaintsDB].[dbo].[Color] C INNER JOIN [NGSPaintsDB].[dbo].[ProductColor] P ON (P.COLORID  =C.ID) INNER JOIN [NGSPaintsDB].[dbo].[tStock] S ON(P.ID= S.ItemID AND C.ID=S.CLRCode) WHERE C.IsDeleted=0  AND P.ProductID=" & intProductID.ToString() & " ORDER BY "
            Dim StrRecordCntSql As String = "SELECT Count(ID) FROM [NGSPaintsDB].[dbo].[Color]"


            'If intColumnIndex = 1 Then
            '    StrSql = StrSql & " ColorCode"
            'Else : StrSql = StrSql & " ColorDescription"
            'End If
            StrSql = StrSql & " ColorDescription"
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@Orderby", IIf(intColumnIndex = 1, "Code", "Name"))

            RecordCount = DbHelper.ExecuteScalar(StrRecordCntSql)
            Return DbHelper.ExecuteDataReader(StrSql)

            'Call DbHelper.ExecuteDataReader(StrSql, Dtr)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemList", StrSql)
            Return Nothing
        End Try
    End Function

    Public Shared Function GetGoDownList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id],[GodownCode],[GodownName] FROM [NGSPaintsDB].[dbo].[Godown] WHERE IsDeleted=0")
    End Function

    'Public Shared Function GetSiteList() As DataTable
    '    Return DbHelper.ExecuteDataTable(" SELECT [Id],[SiteCode],[SiteName] FROM [NGSPaintsDB].[dbo].[Site] WHERE [IsDeleted]=0")
    'End Function
    Public Shared Function GetPriceList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [ID],[PRICELISTCODE],[PRICELISTNAME] FROM [NGSPAINTSDB].[DBO].[PRICELIST] WHERE [ISDELETED]=0")
    End Function
    Public Shared Function GetPackList() As DataTable
        Return DbHelper.ExecuteDataTable("SELECT [Id] AS PACKID ,([PackingContain] + ' '+ [PackingCode]+ ' '+ [PackingDescription]) AS PACK  FROM [NGSPaintsDB].[dbo].[Packing] WHERE [IsDeleted]=0")

    End Function

    Public Shared Function GetOtherCharges() As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT Id,ChargeName,'0' AS Value,0.0 AS Amount,IsTaxable FROM OtherCharge WHERE IsDeleted =0 AND AddToInvoice =1"
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetOtherCharges", strSql)
            Return Nothing
        End Try
    End Function
End Class
